﻿using System;
using System.Collections;
using System.Data;
using System.Globalization;
using System.Xml;

public class CExcelMLFiller
{
    private bool bOperationFailed;
    private CultureInfo ci;
    private string dateTimeFormat;
    private DataSet dsData;
    private ArrayList errorList;
    private static string excelmlPrefix = "urn:schemas-microsoft-com:office:excel";
    private string languageName;
    private XmlNamespaceManager nsmgr;
    private int numberDecimalDigits;
    private string numberDecimalSeparator;
    private NumberFormatInfo numberFormat;
    private string numberGroupSeparator;
    private const string repeatAttribute = "SMLRepeat";
    private static string spreadsheetPrefix = "urn:schemas-microsoft-com:office:spreadsheet";
    private XmlNode tableNode;
    private string templateContent;
    private XmlNodeList templateRowsColl;
    private XmlNode workbookNode;
    private XmlNodeList worksheetTemplates;
    private XmlDocument xmlTemplateDoc;

    public CExcelMLFiller(string templateContent)
    {
        this.nsmgr = new XmlNamespaceManager(new NameTable());
        this.errorList = new ArrayList();
        this.templateContent = templateContent;
        this.dsData = new DataSet();
        this.LoadTemplate();
    }

    public CExcelMLFiller(DataSet dsData, string templateContent)
    {
        this.nsmgr = new XmlNamespaceManager(new NameTable());
        this.errorList = new ArrayList();
        this.dsData = dsData;
        this.templateContent = templateContent;
        this.LoadTemplate();
    }

    private void LoadTemplate()
    {
        this.errorList = new ArrayList();
        this.bOperationFailed = false;
        try
        {
            this.xmlTemplateDoc = new XmlDocument();
            this.xmlTemplateDoc.LoadXml(this.templateContent);
            this.nsmgr.AddNamespace("x", excelmlPrefix);
            this.nsmgr.AddNamespace("ss", spreadsheetPrefix);
            this.workbookNode = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook", this.nsmgr);
            this.worksheetTemplates = this.workbookNode.SelectNodes("ss:Worksheet", this.nsmgr);
            XmlNode node = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook/ss:Names/ss:NamedRange[@ss:Name='LanguageName']/@ss:RefersTo", this.nsmgr);
            if (node != null)
            {
                this.languageName = node.Value;
                this.languageName = this.languageName.Replace("=", string.Empty);
                this.languageName = this.languageName.Replace("\"", string.Empty);
                try
                {
                    this.ci = new CultureInfo(this.languageName, false);
                    this.numberFormat = this.ci.NumberFormat;
                }
                catch
                {
                    this.languageName = null;
                    this.numberFormat = null;
                }
            }
            else
            {
                this.languageName = null;
            }
            node = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook/ss:Names/ss:NamedRange[@ss:Name='DateTimeFormat']/@ss:RefersTo", this.nsmgr);
            if (node != null)
            {
                this.dateTimeFormat = node.Value;
                this.dateTimeFormat = this.dateTimeFormat.Replace("=", string.Empty);
                this.dateTimeFormat = this.dateTimeFormat.Replace("\"", string.Empty);
            }
            else
            {
                this.dateTimeFormat = null;
            }
            node = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook/ss:Names/ss:NamedRange[@ss:Name='NumberDecimalSeparator']/@ss:RefersTo", this.nsmgr);
            if (node != null)
            {
                this.numberDecimalSeparator = node.Value;
                this.numberDecimalSeparator = this.numberDecimalSeparator.Replace("=", string.Empty);
                this.numberDecimalSeparator = this.numberDecimalSeparator.Replace("\"", string.Empty);
                if (this.numberFormat == null)
                {
                    this.numberFormat = new NumberFormatInfo();
                }
                this.numberFormat.NumberDecimalSeparator = (this.numberDecimalSeparator.Trim() == string.Empty) ? "." : this.numberDecimalSeparator;
            }
            else
            {
                this.numberDecimalSeparator = null;
            }
            node = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook/ss:Names/ss:NamedRange[@ss:Name='NumberGroupSeparator']/@ss:RefersTo", this.nsmgr);
            if (node != null)
            {
                this.numberGroupSeparator = node.Value;
                this.numberGroupSeparator = this.numberGroupSeparator.Replace("=", string.Empty);
                this.numberGroupSeparator = this.numberGroupSeparator.Replace("\"", string.Empty);
                if (this.numberFormat == null)
                {
                    this.numberFormat = new NumberFormatInfo();
                }
                this.numberFormat.NumberGroupSeparator = (this.numberGroupSeparator.Trim() == string.Empty) ? "," : this.numberGroupSeparator;
            }
            else
            {
                this.numberGroupSeparator = null;
            }
            node = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook/ss:Names/ss:NamedRange[@ss:Name='NumberDecimalDigits']/@ss:RefersTo", this.nsmgr);
            if (node != null)
            {
                string s = node.Value.Replace("=", string.Empty).Replace("\"", string.Empty);
                try
                {
                    this.numberDecimalDigits = int.Parse(s);
                }
                catch
                {
                    this.numberDecimalDigits = -1;
                }
                if (this.numberFormat == null)
                {
                    this.numberFormat = new NumberFormatInfo();
                }
                this.numberFormat.NumberDecimalDigits = (this.numberDecimalDigits == -1) ? 2 : this.numberDecimalDigits;
            }
            else
            {
                this.numberDecimalDigits = -1;
            }
        }
        catch (Exception innerException)
        {
            while (innerException != null)
            {
                this.errorList.Add(innerException.Message);
                innerException = innerException.InnerException;
            }
            this.bOperationFailed = true;
        }
    }

    public void RemoveWorksheetTemplate()
    {
        foreach (XmlNode node in this.worksheetTemplates)
        {
            this.workbookNode.RemoveChild(node);
        }
    }

    private void ReplaceFieldData(XmlNode baseNode, string fieldName, string data, Type colType)
    {
        this.errorList = new ArrayList();
        this.bOperationFailed = false;
        foreach (XmlNode node2 in baseNode.SelectNodes("ss:Cell[@ss:Formula='=" + fieldName + "']", this.nsmgr))
        {
            XmlNode node = node2.SelectSingleNode("ss:Data", this.nsmgr);
            if (node == null)
            {
                this.errorList.Add("The field data is selected from the fields definition data source or merge document is corrupted!");
                this.bOperationFailed = true;
                break;
            }
            ((XmlElement)node2).RemoveAttribute("ss:Formula");
            if (colType == typeof(DateTime))
            {
                if (this.dateTimeFormat != null)
                {
                    node.InnerText = DateTime.Parse(data).ToString(this.dateTimeFormat);
                }
                else
                {
                    node.InnerText = data;
                }
            }
            else if (((colType == typeof(int)) || (colType == typeof(short))) || (colType == typeof(long)))
            {
                if (this.numberFormat != null)
                {
                    node.InnerText = int.Parse(data).ToString(this.numberFormat);
                }
                else
                {
                    node.InnerText = (data == "") ? "0" : data;
                }
                ((XmlElement)node).SetAttribute("ss:Type", "Number");
            }
            else if (((colType == typeof(decimal)) || (colType == typeof(float))) || (colType == typeof(double)))
            {
                if (this.numberFormat != null)
                {
                    node.InnerText = decimal.Parse(data).ToString("N", this.numberFormat);
                }
                else
                {
                    node.InnerText = (data == "") ? "0" : data;
                }
                ((XmlElement)node).SetAttribute("ss:Type", "Number");
            }
            else
            {
                node.InnerText = data;
                ((XmlElement)node).SetAttribute("ss:Type", "String");
            }
        }
    }

    public void Transform()
    {
        try
        {
            this.tableNode = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook/ss:Worksheet/ss:Table", this.nsmgr);
            this.templateRowsColl = this.xmlTemplateDoc.SelectNodes("/ss:Workbook/ss:Worksheet/ss:Table/ss:Row", this.nsmgr);
            foreach (DataTable table in this.dsData.Tables)
            {
                this.TransformTemplateRows(table);
                this.TransformTemplateGroup(table);
            }
            for (int i = this.templateRowsColl.Count - 1; i >= 0; i--)
            {
                if (this.templateRowsColl[i].SelectSingleNode("ss:Cell[contains(@ss:Formula, 'SMLRepeat')]", this.nsmgr) != null)
                {
                    this.tableNode.RemoveChild(this.templateRowsColl[i]);
                }
            }
            ((XmlElement)this.tableNode).RemoveAttribute("ss:ExpandedRowCount");
        }
        catch (Exception innerException)
        {
            while (innerException != null)
            {
                this.errorList.Add(innerException.Message);
                innerException = innerException.InnerException;
            }
            this.bOperationFailed = true;
        }
    }

    public void Transform(DataSet ds, string sheetName)
    {
        this.Transform(0, ds, sheetName);
    }

    public void Transform(int worksheetTemplateNumber, DataSet ds, string sheetName)
    {
        if (worksheetTemplateNumber <= (this.worksheetTemplates.Count - 1))
        {
            try
            {
                XmlNode newChild = this.worksheetTemplates[worksheetTemplateNumber].Clone();
                (newChild as XmlElement).RemoveAttribute("ss:ExpandedRowCount");
                this.tableNode = newChild.SelectSingleNode("ss:Table", this.nsmgr);
                this.templateRowsColl = newChild.SelectNodes("ss:Table/ss:Row", this.nsmgr);
                foreach (DataTable table in ds.Tables)
                {
                    this.TransformTemplateRows(table);
                    this.TransformTemplateGroup(table);
                }
                for (int i = this.templateRowsColl.Count - 1; i >= 0; i--)
                {
                    if (this.templateRowsColl[i].SelectSingleNode("ss:Cell[contains(@ss:Formula, 'SMLRepeat')]", this.nsmgr) != null)
                    {
                        this.tableNode.RemoveChild(this.templateRowsColl[i]);
                    }
                }
                ((XmlElement)this.tableNode).RemoveAttribute("ss:ExpandedRowCount");
                (newChild as XmlElement).SetAttribute("ss:Name", sheetName);
                this.workbookNode.AppendChild(newChild);
            }
            catch (Exception innerException)
            {
                while (innerException != null)
                {
                    this.errorList.Add(innerException.Message);
                    innerException = innerException.InnerException;
                }
                this.bOperationFailed = true;
            }
        }
    }

    public void Transform_dong()
    {
        try
        {
            this.tableNode = this.xmlTemplateDoc.SelectSingleNode("/ss:Workbook/ss:Worksheet/ss:Table", this.nsmgr);
            this.templateRowsColl = this.xmlTemplateDoc.SelectNodes("/ss:Workbook/ss:Worksheet/ss:Table/ss:Row", this.nsmgr);
            foreach (DataTable table in this.dsData.Tables)
            {
                this.TransformTemplateRows_dong(table);
                this.TransformTemplateGroup(table);
            }
            for (int i = this.templateRowsColl.Count - 1; i >= 0; i--)
            {
                if (this.templateRowsColl[i].SelectSingleNode("ss:Cell[contains(@ss:Formula, 'SMLRepeat')]", this.nsmgr) != null)
                {
                    this.tableNode.RemoveChild(this.templateRowsColl[i]);
                }
            }
            ((XmlElement)this.tableNode).RemoveAttribute("ss:ExpandedRowCount");
        }
        catch (Exception innerException)
        {
            while (innerException != null)
            {
                this.errorList.Add(innerException.Message);
                innerException = innerException.InnerException;
            }
            this.bOperationFailed = true;
        }
    }

    private void TransformTemplateGroup(DataTable dt)
    {
        foreach (XmlNode node in this.templateRowsColl)
        {
            foreach (DataColumn column in dt.Columns)
            {
                string fieldName = dt.TableName + column.ColumnName + "_sum";
                if (node.SelectSingleNode("ss:Cell[@ss:Formula='=" + fieldName + "']", this.nsmgr) != null)
                {
                    this.ReplaceFieldData(node, fieldName, dt.Compute("Sum([" + column.ColumnName + "])", "").ToString(), column.DataType);
                }
            }
        }
    }

    private void TransformTemplateRows(DataTable dt)
    {
        foreach (XmlNode node in this.templateRowsColl)
        {
            string str;
            if (node.SelectSingleNode("ss:Cell[contains(@ss:Formula, '=" + dt.TableName + "SMLRepeat')]", this.nsmgr) != null)
            {
                this.tableNode = node.ParentNode;
                foreach (DataRow row in dt.Rows)
                {
                    XmlNode newChild = node.Clone();
                    ((XmlElement)newChild).RemoveAttribute("ss:Index");
                    XmlNode node2 = newChild.SelectSingleNode("ss:Cell[contains(@ss:Formula, '=" + dt.TableName + "SMLRepeat')]", this.nsmgr);
                    ((XmlElement)node2).RemoveAttribute("ss:Formula");
                    node2.FirstChild.InnerText = string.Empty;
                    this.tableNode.InsertBefore(newChild, node);
                    for (int i = 0; i < row.ItemArray.Length; i++)
                    {
                        str = dt.TableName + dt.Columns[i].ColumnName;
                        this.ReplaceFieldData(newChild, str, row[i].ToString(), dt.Columns[i].DataType);
                    }
                }
            }
            else if (dt.Rows.Count > 0)
            {
                DataRow row2 = dt.Rows[0];
                for (int j = 0; j < row2.ItemArray.Length; j++)
                {
                    str = dt.TableName + dt.Columns[j].ColumnName;
                    this.ReplaceFieldData(node, str, row2[j].ToString(), dt.Columns[j].DataType);
                }
            }
        }
    }

    private void TransformTemplateRows_dong(DataTable dt)
    {
        foreach (XmlNode node in this.templateRowsColl)
        {
            string str;
            if (node.SelectSingleNode("ss:Cell[contains(@ss:Formula, '=" + dt.TableName + "SMLRepeat')]", this.nsmgr) != null)
            {
                this.tableNode = node.ParentNode;
                foreach (DataRow row in dt.Rows)
                {
                    XmlNode newChild = node.Clone();
                    ((XmlElement)newChild).RemoveAttribute("ss:Index");
                    XmlNode node2 = newChild.SelectSingleNode("ss:Cell[contains(@ss:Formula, '=" + dt.TableName + "SMLRepeat')]", this.nsmgr);
                    ((XmlElement)node2).RemoveAttribute("ss:Formula");
                    node2.FirstChild.InnerText = string.Empty;
                    this.tableNode.InsertBefore(newChild, node);
                    for (int i = 0; i < row.ItemArray.Length; i++)
                    {
                        str = dt.TableName + dt.Columns[i].ColumnName;
                        this.ReplaceFieldData(newChild, str, row[i].ToString(), dt.Columns[i].DataType);
                    }
                }
            }
            else if (dt.Rows.Count > 0)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (LIB.Core.LibTable.FindColumn(dt, "ma") >= 0)
                    {
                        for (int k = 0; k < dt.Rows.Count; k++)
                        {
                            str = dt.TableName + dt.Columns[j].ColumnName + dt.Rows[k]["ma"].ToString().Trim();
                            this.ReplaceFieldData(this.xmlTemplateDoc.DocumentElement, str, dt.Rows[k][j].ToString(), dt.Columns[j].DataType);
                        }
                    }
                }
            }
        }
    }

    public ArrayList ErrorList
    {
        get
        {
            return this.errorList;
        }
    }

    public XmlDocument ExcelMLDocument
    {
        get
        {
            return this.xmlTemplateDoc;
        }
    }

    public bool OperationFailed
    {
        get
        {
            return this.bOperationFailed;
        }
    }
}

